/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package EntityDB;

import entity.Author;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import sun.font.Type1Font;

/**
 *
 * @author NgocNam
 */
public class authorDB {
        private String URL = "jdbc:sqlserver://localhost;databaseName=LeaveSystemManager";
        private String Use = "sa"; 
        private String Pass = "123456";
        private Connection conn;
    
    
        public authorDB(){
            try {
                conn = DriverManager.getConnection(URL, Use, Pass);
            } catch (SQLException ex) {
                Logger.getLogger(authorDB.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
        
        public boolean addAuthor(Author a){
        try {
            CallableStatement csmt=conn.prepareCall("{call insertAuthor(?,?,?)}");
            csmt.registerOutParameter(1, Types.INTEGER);
            csmt.setString(2, a.getAuthorName());
            csmt.executeUpdate();
            int authorID=csmt.getInt(1);
            a.setAuthorID(authorID);
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(authorDB.class.getName()).log(Level.SEVERE, null, ex);
        }
                return false;
        }
        
       
        public boolean updateAuhor(Author a){
        try {
            CallableStatement csmt=conn.prepareCall("{call updateAuthor(?,?)}");
            csmt.setInt(1, a.getAuthorID());
            csmt.setString(2, a.getAuthorName());
            int row=csmt.executeUpdate();
            if(row>0){
            return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(authorDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        
        public boolean removeAuthor(int authorID){
        try {
            CallableStatement csmt =conn.prepareCall("{call removeAuthor(?)}");
            csmt.setInt(1, authorID);
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(authorDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        
        }
        
        public ArrayList<Author> getAuthor(){
            ArrayList<Author> list=new ArrayList<Author>();
        try {
            
            CallableStatement csmt=conn.prepareCall("{call getAuthor()}");
            ResultSet rs=csmt.executeQuery();
            while(rs.next()){
                int author=rs.getInt(1);
                String authorName=rs.getString(2);
                Author a=new Author(author, authorName);
                list.add(a);
            }
        } catch (SQLException ex) {
            Logger.getLogger(authorDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return list;
        }
}
